/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package be.contribute.pihome;

import be.contribute.common.pojo.Temperature;
import be.contribute.common.utils.StringUtil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author debian
 */
public class DBController {

    static DBController fDBController;
    private final static String DB_DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
    private final static String DB_NAME = "PiHome";
    private final static String DB_URL = String.format("jdbc:derby:%s", DB_NAME);
    
    private Connection fConnection = null;
    private Statement fStatement = null;
    private ResultSet fResultset = null;

    public DBController() 
    {
        try {
            // Load the embedded derby driver
            Class.forName(DB_DRIVER);
            // Open a connection to the database (create if it does not exist)
            fConnection = DriverManager.getConnection(DB_URL + ";create=true", null);
            fStatement = fConnection.createStatement();
            initDatabase();
            // Greet
            System.out.println("Hello World from JavaDB!");
            } catch (SQLException ex) {
                ex.printStackTrace();
            } catch (ClassNotFoundException ex) {
                ex.printStackTrace();
        }
    }   
    
    private void initDatabase(){
        try {
                fStatement.execute("CREATE TABLE TEMPERATURE (ID INTEGER NOT NULL GENERATED "
                        + "ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                        + "TIME TIMESTAMP NOT NULL,"
                        + "TEMP_VALUE NUMERIC(6,2) NOT NULL,"
                        + "HUMIDITY_VALUE NUMERIC(6,2),"
                        + "ROOM_ID INTEGER NOT NULL)");
            } catch (SQLException ex) {
                if(ex.getMessage().indexOf("already exists") < 1)
                        ex.printStackTrace();
            }
         try {
                fStatement.execute("CREATE TABLE ROOM (ID INTEGER NOT NULL GENERATED "
                        + "ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                        + "NAME VARCHAR(50) NOT NULL,"
                        + "DEVICE_ID INTEGER NOT NULL)");
                fStatement.execute("INSERT INTO ROOM(NAME,DEVICE_ID) VALUES('Living room',1)");
                fStatement.execute("INSERT INTO ROOM(NAME,DEVICE_ID) VALUES('Bathroom',2)");
            } catch (SQLException ex) {
                 if(ex.getMessage().indexOf("already exists") < 1)
                        ex.printStackTrace();
            }
    }     
    
    public static DBController getInstance()
    {
      if(fDBController == null)
            fDBController = new DBController();
      return fDBController;
    }
    
    public void insertIntoTemperature(Temperature pTemp)
    {  
        try {
            Timestamp date = new Timestamp(pTemp.getTime().getTime());
            fStatement.execute("INSERT INTO TEMPERATURE(TIME,TEMP_VALUE,HUMIDITY_VALUE, ROOM_ID) VALUES ('"
                                        + date + "', "+ pTemp.getTempValue() +", "
                                        + ""+pTemp.getHumValue()+", "+pTemp.getRoomId()+")");
            System.out.println("Temperature inserted");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }  
    
    public List<Temperature> getTemperatureFromRoom(Integer pRoomId)
    {  
        try {
            fResultset = fStatement.executeQuery("SELECT * FROM TEMPERATURE WHERE ROOM_ID="+pRoomId);
            Temperature temp;
            List<Temperature> tempList = new ArrayList();
            
            while (fResultset.next()) {
                temp = new Temperature();
                temp.setId(fResultset.getInt("ID"));
                temp.setTempValue(fResultset.getDouble("TEMP_VALUE"));
                temp.setHumValue(fResultset.getDouble("HUMIDITY_VALUE"));
                temp.setTime(fResultset.getTimestamp("TIME"));
                temp.setRoomId(fResultset.getInt("ROOM_ID"));
                tempList.add(temp);          
             }
            return tempList;
            
            }
            catch (SQLException ex) {
                ex.printStackTrace();
            return null;
        }
    }  
    
    public Temperature getCurrentTemperatureFromRoom(Integer pRoomId)
    {  
        List<Temperature> vList = this.getTemperatureFromRoom(pRoomId);
        if(vList.size() > 0)
            return vList.get(vList.size()-1);
        else
            return null;
    }  
    
    public void insertTemperature(String pInput)
    {
        Double temp_value = StringUtil.getDoubleFromString(pInput, "T");
        Double hum_value = StringUtil.getDoubleFromString(pInput, "H");
        Integer room_id = StringUtil.getIntFromString(pInput, "R");
        Temperature vTemp = null;
        if(temp_value != null && hum_value != null)
            vTemp = new Temperature(1,room_id,temp_value, hum_value, new Timestamp(System.currentTimeMillis()));
        try {
            fStatement.execute("INSERT INTO TEMPERATURE(TIME,TEMP_VALUE,HUMIDITY_VALUE, ROOM_ID) VALUES ('"
                                        + vTemp.getTime() + "', "+ vTemp.getTempValue() +", "
                                        + ""+vTemp.getHumValue()+", "+vTemp.getRoomId()+")");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    public String getRoomName(int pRoomId)
    {
         try {
            fResultset = fStatement.executeQuery("SELECT NAME FROM ROOM WHERE ID="+pRoomId);
            
            while (fResultset.next()) 
            {
               return fResultset.getString("NAME");       
            }
            
            }
            catch (SQLException ex) {
                return "Error while fetching room name";
        }
        return "";
    }

}
